package spider.service;

import com.htiiot.common.db.DbConnectionPool;
import com.htiiot.common.db.JDBCUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import spider.http.HTTPUtil;
import spider.pojo.Device;
import spider.pojo.DevicesList;
import spider.util.ApiKey;

import java.sql.*;
import java.util.*;


public class DeviceListService {
    private static Logger logger = LogManager.getLogger(DeviceListService.class);
    public static void getTZDeviceList(){
        List<DevicesList> devicesLists = HTTPUtil.get("http://outwardapi.hence.cn/api/Devices/getList?Token=d04259b9c0a3ccabedae048c1d3bef3f&Company=&Name=&PageIndex=1&PageSize=1500", DevicesList.class);
        if(devicesLists.size() > 0){
            DevicesList devicesList = devicesLists.get(0);
            String rescode = devicesList.getRescode();
            if("00".equals(rescode)){
                DbConnectionPool pool = DbConnectionPool.getPool();
                Connection conn = null;
                PreparedStatement ps = null;
                ResultSet rs = null;
                try {
                    conn = pool.getConnection();
                    conn.setAutoCommit(false);
                    String sql = "insert into tz_device(name,brand,model,company,test_count,photo,lng,lat,state,contanct,telphone,address) values (?,?,?,?,?,?,?,?,?,?,?,?)";
                    ps = conn.prepareStatement(sql);
                    ps.clearBatch();
                    List<Device> deviceList = devicesList.getData();
                    for (int i = 0; i < deviceList.size(); i++) {
                        Device device =  deviceList.get(i);
                        ps.setString(1,device.getName());
                        ps.setString(2,device.getBrand());
                        ps.setString(3,device.getModel());
                        ps.setString(4,device.getCompany());
                        ps.setString(5,device.getTestCount());
                        ps.setString(6,device.getPhoto());
                        ps.setString(7,device.getLng());
                        ps.setString(8,device.getLat());
                        ps.setString(9,device.getState());
                        ps.setString(10,device.getContanct());
                        ps.setString(11,device.getTel());
                        ps.setString(12,device.getAddr());
                        ps.addBatch();
                    }
                    ps.executeBatch();

                } catch (SQLException e) {
                    try {
                        conn.rollback();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                } finally {
                    try {
                        conn.commit();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    JDBCUtils.close(ps, rs, conn);
                }
            }
        }
    }

    public static void updateTZDeviceList(){
        logger.info("update Device List ");
        String url = "http://outwardapi.hence.cn/api/Devices/getList?Token=d04259b9c0a3ccabedae048c1d3bef3f&Company=&Name=&PageIndex=1&PageSize=150";
        logger.info("request TZDeviceList url is : " + url);
        List<DevicesList> devicesLists = HTTPUtil.get(url, DevicesList.class);
        if(devicesLists != null && devicesLists.size() > 0){
            DevicesList devicesList = devicesLists.get(0);
            String rescode = devicesList.getRescode();
            if("00".equals(rescode)){
                DbConnectionPool pool = DbConnectionPool.getPool();
                Connection conn = null;
                PreparedStatement ps = null;
                ResultSet rs = null;
                try {
                    conn = pool.getConnection();
                    conn.setAutoCommit(false);
                    String sql = "insert into tz_device(name,brand,model,company,test_count,photo,lng,lat,state,contanct,telphone,address) values (?,?,?,?,?,?,?,?,?,?,?,?)  ON DUPLICATE KEY UPDATE test_count = ?,state = ?";
                    ps = conn.prepareStatement(sql);
                    ps.clearBatch();
                    List<Device> deviceList = devicesList.getData();
                    for (int i = 0; i < deviceList.size(); i++) {
                        Device device =  deviceList.get(i);
                        logger.info("get device data is --> "+device);
                        ps.setString(1,device.getName());
                        ps.setString(2,device.getBrand());
                        ps.setString(3,device.getModel());
                        ps.setString(4,device.getCompany());
                        ps.setString(5,device.getTestCount());
                        ps.setString(6,device.getPhoto());
                        ps.setString(7,device.getLng());
                        ps.setString(8,device.getLat());
                        ps.setString(9,device.getState());
                        ps.setString(10,device.getContanct());
                        ps.setString(11,device.getTel());
                        ps.setString(12,device.getAddr());
                        ps.setString(13,device.getTestCount());
                        ps.setString(14,device.getState());
                        ps.addBatch();
                    }
                    ps.executeBatch();
                } catch (SQLException e) {
                    try {
                        conn.rollback();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                } finally {
                    try {
                        conn.commit();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    JDBCUtils.close(ps, rs, conn);
                }
            }
        }

    }

    public static void  updateTZTenantIdDeviceId(){
        DbConnectionPool pool = DbConnectionPool.getPool();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = pool.getConnection();
            conn.setAutoCommit(false);
            String updateTenantId = "UPDATE tz_device td,tenant t SET td.tenant_id = t.id WHERE td.company = t.name";
            ps = conn.prepareStatement(updateTenantId);
            ps.executeUpdate();
            String updateDeviceId = "UPDATE tz_device td,thing_device d SET td.device_id = d.id WHERE td.name = d.serial_number and d.type = 0 and td.tenant_id = d.tenant_id";
            ps = conn.prepareStatement(updateDeviceId);
            ps.executeUpdate();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                conn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            JDBCUtils.close(ps, rs, conn);
        }
    }

    public static void insertThingDeviceList(){
        logger.info("update Device List ");
        List<DevicesList> devicesLists = HTTPUtil.get("http://outwardapi.hence.cn/api/Devices/getList?Token=d04259b9c0a3ccabedae048c1d3bef3f&Company=&Name=&PageIndex=1&PageSize=150", DevicesList.class);
        if(devicesLists != null && devicesLists.size() > 0){
            DevicesList devicesList = devicesLists.get(0);
            String rescode = devicesList.getRescode();
            if("00".equals(rescode)){
                HashSet<String> companySet = new HashSet<>();
                HashMap<String,Device> companyDeviceMap = new HashMap<>();
                List<Device> insertDevice = new ArrayList<>();
                List<Device> insertDeviceContainTenantId = new ArrayList<>();
                List<String> insertTenant = new ArrayList<>();
                List<Integer> insertTenantApiKey = new ArrayList<>();
                DbConnectionPool pool = DbConnectionPool.getPool();
                Connection conn = null;
                PreparedStatement ps = null;
                ResultSet rs = null;
                try {
                    conn = pool.getConnection();
                    conn.setAutoCommit(false);
                    List<Device> deviceList = devicesList.getData();
                    for (int i = 0; i < deviceList.size(); i++) {
                        Device device =  deviceList.get(i);
                        companySet.add(device.getCompany());
                        if(!companyDeviceMap.containsKey(device.getCompany()+device.getName())){
                            companyDeviceMap.put(device.getCompany()+device.getName(),device);
                        }
                    }

                    //查询去重租户是否存在
                    Iterator<String> iterator = companySet.iterator();
                    while (iterator.hasNext()) {
                        String companyName =  iterator.next();
                        String searchTenantSql = "select count(1) from tenant where name = '" + companyName+"'";
                        ps = conn.prepareStatement(searchTenantSql);
                        rs = ps.executeQuery();
                        if(rs.next()){
                            if(rs.getInt(1)==0){
                                insertTenant.add(companyName);
                            }
                        }
                    }

                    //插入不存在租户，需要租户id
                    if(insertTenant.size() > 0){

                        for (int i = 0; i < insertTenant.size(); i++) {
                            String companyName =  insertTenant.get(i);
                            String insertTenantSql = "insert into tenant (name,type,status,deleted,create_time) values ('"+ companyName +"',1,0,0,now())";
                            ps = conn.prepareStatement(insertTenantSql, Statement.RETURN_GENERATED_KEYS);
                            ps.executeUpdate();
                            rs = ps.getGeneratedKeys();
                            if(rs.next()){
                                insertTenantApiKey.add(rs.getInt(1));
                            }
                        }
                    }

                    //更新租户apiKey
                    if(insertTenantApiKey.size() > 0){
                        String updateApiKey = "update tenant set api_key = ? where id = ?";
                        ps = conn.prepareStatement(updateApiKey);
                        ps.clearBatch();
                        for (int i = 0; i < insertTenantApiKey.size(); i++) {
                            Integer tenantId =  insertTenantApiKey.get(i);
                            ps.setInt(1,tenantId);
                            ps.setString(2, ApiKey.genKeyForTenant(tenantId));
                            ps.addBatch();
                        }
                        ps.executeBatch();
                    }

                    //更新去重后设备的tenant_id
                    for (Map.Entry<String,Device> entry: companyDeviceMap.entrySet()) {
                        Device device = entry.getValue();
                        String searchTenantIdSql = "select id from tenant where name = '"+device.getCompany()+"'";
                        ps = conn.prepareStatement(searchTenantIdSql);
                        rs = ps.executeQuery();
                        if(rs.next()){
                            device.setTenant_id(rs.getInt(1));
                            insertDeviceContainTenantId.add(device);
                        }
                    }

                    //查看设备是否存在
                    for (Device device : insertDeviceContainTenantId) {
                        String searchDeviceSql = "select count(1) from thing_device where type = 0 and serial_number = '" + device.getName() + "' and tenant_id = " + device.getTenant_id();
                        rs = ps.executeQuery(searchDeviceSql);
                        if(rs.next()){//设备不存在插入
                            if(rs.getInt(1)==0){
                                insertDevice.add(device);
                            }
                        }
                    }

                    //插入不存在设备
                    if(insertDevice.size() > 0){
                        String insertDeviceSql = "insert into thing_device(serial_number,brand,model,tenant_id,location,status,address,name,avatar,type,create_time,deleted,struct_id,parent_id,group_id) values (?,?,?,?,?,?,?,?,?,0,now(),false,0,0,0)";
                        ps = conn.prepareStatement(insertDeviceSql);
                        ps.clearBatch();
                        for (int i = 0; i < insertDevice.size(); i++) {
                            Device device =  insertDevice.get(i);
                            ps.setString(1,device.getName());
                            ps.setString(2,device.getBrand());
                            ps.setString(3,device.getModel());
                            ps.setInt(4,device.getTenant_id());
                            ps.setString(5,device.getLng()+","+device.getLat());
                            ps.setString(6,device.getState());
                            ps.setString(7,device.getAddr());
                            ps.setString(8,device.getName());
                            ps.setString(9,device.getPhoto());
                            ps.addBatch();
                        }
                        ps.executeBatch();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                    try {
                        conn.rollback();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                } finally {
                    try {
                        conn.commit();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    JDBCUtils.close(ps, rs, conn);
                }
            }
        }
    }
}
